GO
	if exists (select * from sysobjects WHERE name = 'tr_NextId_NV' AND type = 'TR')
		drop trigger tr_NextId_NV
	GO
	create  trigger tr_NextId_NV on NHANVIEN
	for insert
	as
		begin
			DECLARE @lastid nvarchar(10)
			SELECT TOP 1 @lastid = MANV from NHANVIEN order by MANV desc
			UPDATE NHANVIEN set MANV = dbo.fn_NextID (@lastid,'NV',4) where MANV = ''
		end 
	create procedure PRCSelectAllNHANVIEN 
	as
	begin
		SELECT	NV.MANV,
				NV.TENNV,
				NV.GIOITINH,
				(CONVERT(varchar, NV.NAMSINH, 103))NAMSINH,
				NV.CMND,
				NV.SDT,
				(CONVERT(varchar, NV.NGAYVLV, 103))NGAYVLV,
				(SELECT TD.TENTD FROM TRINHDO TD WHERE TD.MATD=NV.MATD) TRINHDO,
				(SELECT PB.TENPB FROM PHONGBAN PB WHERE PB.MAPB=NV.MAPB)PHONGBAN
		FROM NHANVIEN NV
	end
	EXEC PRCSelectAllNHANVIEN
	
	CREATE PROCEDURE PRCInsertNHANVIEN
	   (@MATD nvarchar(5),
		@MAPB nvarchar(5),
		@TENNV nvarchar(30),
		@GIOITINH nvarchar(3),
		@NAMSINH DATETIME,
		@CMND NVARCHAR(9),
		@SDT NVARCHAR(11),
		@NGAYVLV DATETIME
		) 
	AS
	BEGIN
	   INSERT INTO NHANVIEN 
	   VALUES ('',@MATD, @MAPB,@TENNV,@GIOITINH,@NAMSINH,@CMND,@SDT,@NGAYVLV)
	END
	EXEC PRCInsertNHANVIEN 'TD01','PB01','NGUYEN MINH TUAN','NAM','04/07/1994','3345678921','09087654321','04/07/2014'
	EXEC PRCSelectAllNHANVIEN
	SELECT * FROM NHANVIEN
	CREATE PROCEDURE PRCUpdateFloor
	   (@IdF nvarchar(10),
		@IdA nvarchar(10),
		@Name nvarchar(50)
		) 
		AS
		BEGIN
			UPDATE Floor
					SET
					IdA = @IdA,
					Name=@Name
			WHERE IdF = @IdF
		END
		CREATE PROCEDURE PRCUpdateSFloor
	   (@IdF nvarchar(10)
		) 
		AS
		BEGIN
			UPDATE Floor
					SET
					Status = 0
			WHERE IdF = @IdF
		END
		CREATE PROC PRCSelectAllPHONGBAN
		AS
		BEGIN
		 SELECT * FROM PHONGBAN
		END
		CREATE PROC PRCSelectAllTRINHDO
		AS
		BEGIN
			SELECT * FROM TRINHDO
		END